﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KBVault.DAL.Entities;
using Codebricks.Db;
using Codebricks.Db.Extensions;
using System.Data;

namespace KBVault.DAL.Repository
{
    public class CategoryRepository:BaseRepository
    {
        private static string ListAllArticlesCommand = "Select * From articles where Category_Id=@Category_Id";
        private static string GetArticleCountCommand = "Select count(*) as Article_Count From articles where Is_Draft=0 And Category_Id=@Category_Id";
        private static string GetPublicArticleCountCommand = "Select count(*) as Article_Count From articles where Is_Draft=0 And DATE(CURDATE())>=DATE(Publish_Start_Date) And DATE(CURDATE())<DATE(Publish_End_Date) And Category_Id=@Category_Id";
        private static string ListHotCategoriesCommand = "Select * From categories where Is_Hot=1 order by Name";
        private static string ListAllCommand = "Select * From categories order by Name";

        public static void Create( ref Category cat)
        {
            try
            {
                cat.Id = (int)MySqlDb.Insert(cat);
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
            }
        }

        public static void Update(Category cat)
        {
            try
            {
                MySqlDb.Update(cat);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                throw;
            }
        }

        public static void Delete(int id)
        {
            try
            {
                Category c = new Category() { Id = id };
                MySqlDb.Delete(c);
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }

        public static Category Get( int id )
        {
            try
            {
                Category c = new Category() { Id = id };
                return MySqlDb.Get<Category>(c);
                
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }

        public static List<Category> List()
        {
            try
            {                
                List<Category> categories= new List<Category>();
                DataTable dt = MySqlDb.Select(ListAllCommand, null);
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        Category c = new Category();
                        c.CreateObjectFromDataRow(row);
                        categories.Add(c);
                    }
                }
                return categories;
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }

        public static List<Category> ListHotCategories()
        {
            try
            {
                List<Category> categories = new List<Category>();
                DataTable dt = MySqlDb.Select(ListHotCategoriesCommand, null);
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        Category c = new Category();
                        c.CreateObjectFromDataRow(row);
                        categories.Add(c);
                    }
                }
                return categories;
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }

        public static List<Article> Articles(int categoryId)
        {
            try
            {
                List<Article> articles = new List<Article>();
                MysqlParameterCollection mpc = new MysqlParameterCollection();
                mpc.Add("Category_Id",categoryId);
                DataTable dt = MySqlDb.Select(ListAllArticlesCommand, mpc);
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        Article a = new Article();
                        a.CreateObjectFromDataRow(row);
                        articles.Add(a);
                    }
                }
                return articles;
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }
        
        public static int ArticleCount(int categoryId)
        {
            try
            {                
                MysqlParameterCollection mpc = new MysqlParameterCollection();
                mpc.Add("Category_Id", categoryId);
                DataTable dt = MySqlDb.Select(GetArticleCountCommand, mpc);
                if (dt.Rows.Count == 1 )
                {
                    return Convert.ToInt32(dt.Rows[0]["Article_Count"]);
                }
                return 0;
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }

        public static int PublicArticleCount(int categoryId)
        {
            try
            {
                MysqlParameterCollection mpc = new MysqlParameterCollection();
                mpc.Add("Category_Id", categoryId);
                DataTable dt = MySqlDb.Select(GetPublicArticleCountCommand, mpc);
                if (dt.Rows.Count == 1)
                {
                    return Convert.ToInt32(dt.Rows[0]["Article_Count"]);
                }
                return 0;
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }

    }
}
